﻿CREATE FUNCTION [dbo].[fs_BuyBack_Base](
	@StateID int
)RETURNS TABLE
AS

RETURN 
SELECT
L_B.LoanID,
L_B.UsixID,
L_B.FirstName AS [First Name],
L_B.LastName AS [Last Name], 
CONVERT(varchar,BB.StatusDate,111) AS [Date In],
ISNULL(NULLIF(L_B.Dealer,''),CF_E.Dealer) Dealer,
L_B.ContractHolder
FROM v_List_Base L_B
INNER JOIN t_BuyBack BB ON BB.LoanID = L_B.LoanID
INNER JOIN dbo.fs_BuyBack_Read(DEFAULT, @StateID) AS fs_Loans
	ON L_B.LoanID = fs_Loans.LoanID
INNER JOIN usix.v_CustomerFile_Export_List CF_E ON CF_E.LoanID = L_B.LoanID
/*
INNER JOIN
                          (SELECT     LoanID, MenuID, COUNT(*) AS Count
                            FROM          t_Loan_Menus
                            GROUP BY LoanID, MenuID
                            HAVING      (MenuID = @MenuID)) AS MENU_COUNT ON L_B.LoanID = MENU_COUNT.LoanID LEFT OUTER JOIN
                          (SELECT     LoanID, MAX(DateStamp) AS DateStamp
                            FROM          t_Notes AS t_Notes_1
                            GROUP BY LoanID) AS t_Notes ON L_B.LoanID = t_Notes.LoanID
*/









